﻿using System;
using System.Collections.Generic;
using TpBaseObjects.Properties;
using Microsoft.Office.Interop.Excel;
using TpLogger;
using TpExtensions;
using TpBaseObjects.Objects;
using System.Reflection;
using System.Diagnostics;

namespace TpBaseObjects.Base {

    public class ProjectSheet : AbstractProjectSheet {

        #region Members

        //private Worksheet MySheet.;
        //private List<ProjectRow> myRows = new List<ProjectRow>();
        //public delegate int GetFreeNumber(string bereich, Worksheet sheet);
        private List<DateTime> lieferscheinDates = new List<DateTime>();
        private List<DateTime> abnahmeProtokollDates = new List<DateTime>();
        private List<DateTime> durchfuehrungsDates = new List<DateTime>();
        private DateTime auftragsBestaetigungsDatum = new DateTime(1900, 1, 1);
        private bool reparatur = false;
        private List<AkontoObj> akontoList = new List<AkontoObj>();
        private List<InvoiceObj> invoiceList = new List<InvoiceObj>();
        private List<MahnungObj> mahnList = new List<MahnungObj>();
        private List<AkontoObj> zahlungsList = new List<AkontoObj>();
        //private Workbook myBook;

        #endregion

        #region Contructor


        public ProjectSheet(Worksheet sh, bool loadData)
            : base(sh) {

            SetRowNumbers();
            if (loadData) {
                LoadDurchführungsDates();
                LoadAuftragsDates();
                LoadLieferscheinDates();
                LoadAbnahmeprotokollDates();
                LoadAbnahmeprotokollDatesRep();
                LoadLieferscheinDatesRep();
                LoadCheckReparatur();
                LoadAkontos();
                LoadInvoices();
                LoadZahlungen();
                LoadMahnungen();
            }
        }

        public ProjectSheet(Worksheet sh, bool loadData, bool addRows)
            : this(sh, loadData) {
            int startRow = 3;
            if (addRows) {
                if (IsCalcSheet) {
                    startRow = Settings.Default.ZeileStart;
                }
                this.AddAllSheetRows(startRow, IsCalcSheet, false, false);
            }
        }

        public ProjectSheet(Worksheet sh, bool loadData, bool onlyWithJobDate, bool intern)
            : this(sh, loadData) {
            int startRow = 3;

            if (IsCalcSheet) {
                startRow = Settings.Default.ZeileStart;
            }
            this.AddAllSheetRows(startRow, IsCalcSheet, intern, onlyWithJobDate);

        }

        #endregion

        #region Properties

        #region AepüWerte

        #region Erlös

        public double AeErlösMinderung {
            get {
                if (ExistsName("ErlösMinderung")) {
                    Range r1 = MySheet.Range["ErlösMinderung"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeForderungsausfall {
            get {
                if (ExistsName("ErlösForderungsausfall")) {
                    Range r1 = MySheet.Range["ErlösForderungsausfall"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeRabattFix {
            get {
                if (ExistsName("ErlösRabattFix")) {
                    Range r1 = MySheet.Range["ErlösRabattFix"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeRabattLeistungenFix {
            get {
                if (ExistsName("ErlösRabattLeistungenFix")) {
                    Range r1 = MySheet.Range["ErlösRabattLeistungenFix"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeRabattLeistungenProzent {
            get {
                if (ExistsName("ErlösRabattLeistungenProzent")) {
                    Range r1 = MySheet.Range["ErlösRabattLeistungenProzent"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeRabattProdukteFix {
            get {
                if (ExistsName("ErlösRabattProdukteFix")) {
                    Range r1 = MySheet.Range["ErlösRabattProdukteFix"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeRabattProdukteProzent {
            get {
                if (ExistsName("ErlösRabattProdukteProzent")) {
                    Range r1 = MySheet.Range["ErlösRabattProdukteProzent"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeRabattProzent {
            get {
                if (ExistsName("ErlösRabattProzent")) {
                    Range r1 = MySheet.Range["ErlösRabattProzent"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeSchadenfall {
            get {
                if (ExistsName("ErlösSchadenfall")) {
                    Range r1 = MySheet.Range["ErlösSchadenfall"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeSummeHandelsware {
            get {
                if (ExistsName("ErlösSummeHandelsware")) {
                    Range r1 = MySheet.Range["ErlösSummeHandelsware"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeSummeProduktion {
            get {
                if (ExistsName("ErlösSummeProduktion")) {
                    Range r1 = MySheet.Range["ErlösSummeProduktion"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeSummeMontage {
            get {
                if (ExistsName("ErlösSummeMontage")) {
                    Range r1 = MySheet.Range["ErlösSummeMontage"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzRabattProzent {
            get {
                if (ExistsName("ErlösZusatzRabattProzent")) {
                    Range r1 = MySheet.Range["ErlösZusatzRabattProzent"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        #endregion

        #region Kosten

        public double AeKostenSummeHandelsware {
            get {
                if (ExistsName("KostenSummeHandelsware")) {
                    Range r1 = MySheet.Range["KostenSummeHandelsware"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeKostenSummeProduktion {
            get {
                if (ExistsName("KostenSummeProduktion")) {
                    Range r1 = MySheet.Range["KostenSummeProduktion"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeKostenSummeMontage {
            get {
                if (ExistsName("KostenSummeMontage")) {
                    Range r1 = MySheet.Range["KostenSummeMontage"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeKostenSummeAdministration {
            get {
                if (ExistsName("KostenSummeAdministration")) {
                    Range r1 = MySheet.Range["KostenSummeAdministration"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        #endregion

        #region Zusatzkosten

        public double AeZusatzKostenBaustelleninfrastrukturProzent {
            get {
                if (ExistsName("ZusatzKostenBaustelleninfrastrukturProzent")) {
                    Range r1 = MySheet.Range["ZusatzKostenBaustelleninfrastrukturProzent"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzKostenBaustelleninfrastrukturFix {
            get {
                if (ExistsName("ZusatzKostenBaustelleninfrastrukturFix")) {
                    Range r1 = MySheet.Range["ZusatzKostenBaustelleninfrastrukturFix"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzKostenProvisionsErlösFix {
            get {
                if (ExistsName("ZusatzKostenProvisionsErlösFix")) {
                    Range r1 = MySheet.Range["ZusatzKostenProvisionsErlösFix"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzKostenProvisionsErlösProzent {
            get {
                if (ExistsName("ZusatzKostenProvisionsErlösProzent")) {
                    Range r1 = MySheet.Range["ZusatzKostenProvisionsErlösProzent"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzkostenErfüllungsgarantie {
            get {
                if (ExistsName("ZusatzkostenErfüllungsgarantie")) {
                    Range r1 = MySheet.Range["ZusatzkostenErfüllungsgarantie"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzkostenHaftrücklass {
            get {
                if (ExistsName("ZusatzkostenHaftrücklass")) {
                    Range r1 = MySheet.Range["ZusatzkostenHaftrücklass"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzkostenProvsionDB {
            get {
                if (ExistsName("ZusatzkostenProvsionDB")) {
                    Range r1 = MySheet.Range["ZusatzkostenProvsionDB"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZusatzkostenSkonto {
            get {
                if (ExistsName("ZusatzkostenSkonto")) {
                    Range r1 = MySheet.Range["ZusatzkostenSkonto"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        #endregion

        #region Zeitaufwand

        public double AeZeitaufwandAdminsitration {
            get {
                if (ExistsName("ZeitaufwandAdminsitration")) {
                    Range r1 = MySheet.Range["ZeitaufwandAdminsitration"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZeitaufwandMontage {
            get {
                if (ExistsName("ZeitaufwandMontage")) {
                    Range r1 = MySheet.Range["ZeitaufwandMontage"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZeitaufwandProdukte {
            get {
                if (ExistsName("ZeitaufwandProdukte")) {
                    Range r1 = MySheet.Range["ZeitaufwandProdukte"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        public double AeZeitaufwandProdutktion {
            get {
                if (ExistsName("ZeitaufwandProdutktion")) {
                    Range r1 = MySheet.Range["ZeitaufwandProdutktion"];
                    double ret = 0.0;
                    if (r1.Value != null) {
                        Double.TryParse(r1.Value.ToString(), out ret);
                    }
                    return ret;
                }
                return 0;
            }
        }

        #endregion

        #endregion

        public double Akonto {
            get {
                double ret = 0.0;
                if (!ExistsName("Akonto1")) {
                    int currentRow = 301;
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[currentRow, "AF"];
                    double ak1 = 0.0;
                    object toFind = "Akonto";
                    currentRow = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    int firstRow = currentRow;
                    while (currentRow != 0) {
                        if (currentRow > 0) {
                            Range rDat = MySheet.Cells[currentRow, "AR"];
                            if (rDat.Value != null) {
                                if (double.TryParse(rDat.Value.ToString(), out ak1)) {
                                    ret += ak1;
                                }
                            }

                        }
                        aCell = MySheet.Cells[currentRow, "AF"];
                        currentRow = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (currentRow == firstRow) {
                            return ret;
                        }
                    }
                    return ret;
                }

                if (ExistsName("Akonto1")) {
                    double a1 = 0.0;
                    Range rDat = MySheet.Range["Akonto1"];
                    if (rDat.Value != null) {
                        if (double.TryParse(rDat.Value.ToString(), out a1)) {
                            ret += a1;
                        }
                    }
                }

                if (ExistsName("Akonto2")) {
                    double a2 = 0.0;
                    Range rDat = MySheet.Range["Akonto2"];
                    if (rDat.Value != null) {
                        if (double.TryParse(rDat.Value.ToString(), out a2)) {
                            ret += a2;
                        }
                    }
                }

                if (ExistsName("Akonto3")) {
                    double a3 = 0.0;
                    Range rDat = MySheet.Range["Akonto2"];
                    if (rDat.Value != null) {
                        if (double.TryParse(rDat.Value.ToString(), out a3)) {
                            ret += a3;
                        }
                    }
                }

                return ret;
            }
        }

        public List<AkontoObj> AkontoList {
            get { return this.akontoList; }
        }

        public DateTime AkontoDatum {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (!ExistsName("Akonto1")) {
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[301, "AF"];
                    object toFind = "Akonto";
                    int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = MySheet.Cells[row, "AH"];
                        return retBrutto.Value;
                    }
                    return ret;
                }
                Range t1 = MySheet.Range["Akonto1"];
                Range t2 = MySheet.Cells[t1.Row, "AH"];
                if (t2.Value == null) {
                    return ret;
                }
                return t2.Value;
            }
        }

        public string AkontoBeleg {
            get {
                string ret = "";
                if (!ExistsName("Akonto1")) {
                    return ret;
                }
                Range t1 = MySheet.Range["Akonto1"];
                Range t2 = MySheet.Cells[t1.Row, "AO"];
                if (t2.Value == null) {
                    return ret;
                }
                return t2.Value;
            }
        }

        public DateTime ApDate1 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (abnahmeProtokollDates.Count > 0) {
                    abnahmeProtokollDates.Sort();
                    int anz = abnahmeProtokollDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = abnahmeProtokollDates[0 + anz - row];
                }
                return ret;
            }
        }

        public DateTime ApDate2 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (abnahmeProtokollDates.Count > 1) {
                    abnahmeProtokollDates.Sort();
                    int anz = abnahmeProtokollDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = abnahmeProtokollDates[1 + anz - row];
                }
                return ret;
            }
        }

        public DateTime ApDate3 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (abnahmeProtokollDates.Count > 2) {
                    abnahmeProtokollDates.Sort();
                    int anz = abnahmeProtokollDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = abnahmeProtokollDates[2 + anz - row];
                }
                return ret;
            }
        }

        public DateTime AuftragsDatum {
            get {
                Range r1 = MySheet.Range[Settings.Default.ZelleAuftragsDatum];
                if ((r1.Value.ToString().Contains("Auf")) | (r1.Value == null)) {
                    return new DateTime(1900, 1, 1);
                }
                return Extensions.Nz(r1.Value, new DateTime(1900, 1, 1));
            }
            set {
                Range r1 = MySheet.Range[Settings.Default.ZelleAuftragsDatum];
                r1.Value = value;
            }
        }

        public DateTime AuftragsBestaetigung {
            get {
                return auftragsBestaetigungsDatum;
            }
        }

        public int AnzahlZeilen {
            get { return MyRows.Count; }
        }

        public string CalcCharacter {
            get {
                if (!Extensions.isNumeric(MySheet.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                    if (CalcType == "R") {
                        return "a";
                    }
                    return MySheet.Name.Right(1);
                }
                return "";
            }
        }

        public int CalcNumber {
            get {
                int calcNr = 0;
                if (CalcType == "K") {
                    calcNr = Convert.ToInt32(Extensions.Mid(SheetName.ToString(), SheetName.IndexOf("k") + 1));
                }
                if ((CalcType == "KA") | (CalcType == "R")) {
                    calcNr = 1;
                }
                return calcNr;
            }
        }

        public string CalcType {
            get {
                if (MySheet.Name.Contains("k") & !Extensions.isNumeric(MySheet.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                    return "KA";
                }
                if (MySheet.Name.Contains("r")) {
                    return "R";
                }
                if (MySheet.Name.Contains("k") & Extensions.isNumeric(MySheet.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                    return "K";
                }
                return "";
            }
        }

        public DateTime DurchfuehrungVon {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (durchfuehrungsDates.Count > 0) {
                    durchfuehrungsDates.Sort();
                    ret = durchfuehrungsDates[0];
                }
                return ret;
            }
        }

        public DateTime DurchfuehrungBis {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (durchfuehrungsDates.Count > 0) {
                    durchfuehrungsDates.Sort();
                    int anz = durchfuehrungsDates.Count;
                    ret = durchfuehrungsDates[anz - 1];
                }
                return ret;
            }
        }

        public bool HasNames {
            get {
                foreach (Name na in MySheet.Names) {
                    if (na.Name.Contains("SummeBrutto")) {
                        return true;
                    }
                }
                foreach (Name na in MySheet.Parent.Names) {
                    if (na.Name.Contains("SummeBrutto")) {
                        return true;
                    }
                }
                return false;
            }
        }

        public bool InternesProjekt {
            get {
                Range r1 = MySheet.Cells[2, 1];
                string t1 = "";
                if (r1.Value != null) {
                    t1 = Convert.ToString(r1.Value);
                } else {
                    t1 = "";
                }
                if ((t1.ToUpper() == "IP")) {
                    return true;
                }
                return false;
            }
        }

        public List<InvoiceObj> InvoiceList {
            get { return this.invoiceList; }
        }

        public bool IsCalcSheet {
            get {
                Range r1 = MySheet.Cells[1, 1];
                Range r2 = MySheet.Cells[1, 3];
                Range r3 = MySheet.Cells[2, 3];
                Range r4 = MySheet.Cells[2, 1];
                string t1 = "";
                string t2 = "";
                string t3 = "";
                string t4 = "";
                if (r1.Value != null) {
                    t1 = Convert.ToString(r1.Value);
                } else {
                    t1 = "";
                }
                if (r2.Value != null) {
                    t2 = Convert.ToString(r2.Value);
                } else {
                    t2 = "";
                }
                if (r3.Value != null) {
                    t3 = Convert.ToString(r3.Value);
                } else {
                    t3 = "";
                }
                if (r4.Value != null) {
                    t4 = Convert.ToString(r4.Value);
                } else {
                    t4 = "";
                }
                if (((t1 == "Ka") | ((t2 == "Kunde")) & (t3 != "Name")) | (t4.ToUpper() == "IP")) {
                    return true;
                }
                return false;
            }
        }

        public bool IsReparatur {
            get {
                return this.reparatur;
            }
            set {
                this.reparatur = value;
            }
        }

        public double Kilometer {
            get {
                Range r1 = MySheet.Range[Settings.Default.ZelleKilometer];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
            set {
                Range r1 = MySheet.Range[Settings.Default.ZelleKilometer];
                r1.Value = value;
            }
        }

        public double KilometerDifferenz {
            get {
                return (Kilometer - KilometerNachPlz);
            }
        }

        public double KilometerNachPlz {
            get {
                Range r1 = MySheet.Range[Settings.Default.ZelleKmPlz];
                if (r1.Value == null) {
                    return Kilometer;
                }
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0) / 2;
                }
                return 0;
            }
        }

        public double Kundengruppe {
            get {
                Range r1 = MySheet.Range[Settings.Default.ZelleKundengruppe];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public string Kundenname {
            get {
                string ret = "";
                Range r1 = MySheet.Range[Settings.Default.ZelleKundenname];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
        }

        public DateTime LsDate1 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (lieferscheinDates.Count > 0) {
                    lieferscheinDates.Sort();
                    int anz = lieferscheinDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = lieferscheinDates[0 + anz - row];
                }
                return ret;
            }
        }

        public DateTime LsDate2 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (lieferscheinDates.Count > 1) {
                    lieferscheinDates.Sort();
                    int anz = lieferscheinDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = lieferscheinDates[1 + anz - row];
                }
                return ret;
            }
        }

        public DateTime LsDate3 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (lieferscheinDates.Count > 2) {
                    lieferscheinDates.Sort();
                    int anz = lieferscheinDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = lieferscheinDates[2 + anz - row];
                }
                return ret;
            }
        }

        public List<MahnungObj> Mahnungen {
            get { return this.mahnList; }
        }

        public DateTime MaxMahnungsDatum {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                foreach (MahnungObj md in mahnList) {
                    if (md.Date > ret) {
                        ret = md.Date;
                    }
                }
                return ret;
            }
        }

        public DateTime maxZahlungsDatum {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                foreach (AkontoObj za in zahlungsList) {
                    if (za.Date > ret) {
                        ret = za.Date;
                    }
                }
                return ret;
            }
        }

        //public List<ProjectRow> MyRows {
        //    get { return myRows; }
        //}

        //public Worksheet MySheet {
        //    get {
        //        return MySheet;
        //    }
        //}

        //public Workbook MyWorkBook {
        //    get {
        //        return myBook;
        //    }
        //}

        public string OrgName {
            get {
                string ret = "";
                Range r1 = MySheet.Cells[2, 3];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
            set {
                Range r1 = MySheet.Cells[2, 3];
                r1.Value = value;
            }
        }

        public string Ort {
            get {
                string ret = "";
                Range r1 = MySheet.Range[Settings.Default.ZelleOrt];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
            set {
                Range r1 = MySheet.Range[Settings.Default.ZelleOrt];
                r1.Value = value;
            }
        }

        public double Plz {
            get {
                Range r1 = MySheet.Range[Settings.Default.ZellePlz];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
            set {
                Range r1 = MySheet.Range[Settings.Default.ZellePlz];
                r1.Value = value;
            }
        }

        public bool PlzExists {
            get {
                Range r1 = MySheet.Range[Settings.Default.ZellePlzExists];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0) != 0;
                }
                return false;
            }
        }

        public string Projekt {
            get {
                string ret = "";
                Range r1 = MySheet.Cells[2, "D"];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
            set {
                Range r1 = MySheet.Range[Settings.Default.ZelleProjektbezeichnung];
                r1.Value = value;
            }
        }

        public int ProjektNummer {
            get {
                int proNr = 0;
                string proj = MySheet.Name.Substring(1);
                string n1 = Extensions.Left(proj, 2);
                if (Extensions.isNumeric(n1, System.Globalization.NumberStyles.Any)) {
                    proNr = Convert.ToInt32(n1);
                    return proNr;
                }
                n1 = Extensions.Left(proj, 1);
                if (Extensions.isNumeric(n1, System.Globalization.NumberStyles.Any)) {
                    proNr = Convert.ToInt32(n1);
                    return proNr;
                }
                return proNr;
            }
        }

        public double RabattProzent {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösRabattProzent")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Rabatt %";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösRabattProzent"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double RabattZusatzProzent {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösZusatzRabattProzent")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Zusatzrabatt %";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösZusatzRabattProzent"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double RabattFix {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösRabattFix")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Rabatt fix";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösRabattFix"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double RabattProdukteProzent {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösRabattProdukteProzent")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Rabatt Produkte %";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösRabattProdukteProzent"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double RabattLeistungenProzent {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösRabattLeistungenProzent")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Rabatt Leistungen %";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösRabattLeistungenProzent"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double RabattProdukteFix {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösRabattProdukteFix")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Rabatt Produkte fix";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösRabattProdukteFix"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double RabattLeistungenFix {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösRabattLeistungenFix")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Rabatt Leistungen fix";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösRabattLeistungenFix"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public string SheetName {
            get { return MySheet.Name; }
        }

        public int SheetType {
            get {
                string sheetType = MySheet.Name.Substring(1);
                foreach (CustomProperty p in MySheet.CustomProperties) {
                    if (p.Name == "SheetType") {
                        object ret = p.Value;
                        return Convert.ToInt32(p.Value);
                    }
                }
                if (sheetType.Contains("k")) {
                    return (int)Extensions.SheetType.Kalkulation;
                }
                if (sheetType.Contains("r")) {
                    return (int)Extensions.SheetType.Abrechnung; //***
                }
                if (sheetType.Contains("aa") | sheetType.Contains("ab") | sheetType.Contains("ac") | sheetType.Contains("ad") | sheetType.Contains("ae") | sheetType.Contains("af") | sheetType.Contains("ag")) {
                    return (int)Extensions.SheetType.Auftragsbestaetigung; //***
                }
                if (sheetType.Contains("b")) {
                    return (int)Extensions.SheetType.Projektbericht; //***
                }
                if (sheetType.Contains("l")) {
                    return (int)Extensions.SheetType.Lieferschein;
                }
                if (sheetType.Contains("f")) {
                    return (int)Extensions.SheetType.Rechnung; //***
                }
                if (sheetType.Contains("p")) {
                    return (int)Extensions.SheetType.Abnahmeprotokoll; //***
                }
                if (sheetType.Contains("ka") | sheetType.Contains("kb") | sheetType.Contains("kc") | sheetType.Contains("kd") | sheetType.Contains("ke") | sheetType.Contains("kf") | sheetType.Contains("kg")) {
                    return (int)Extensions.SheetType.Kalkulation;
                }
                if (sheetType.Contains("a")) {
                    return (int)Extensions.SheetType.Angebot; //***
                }
                return 0;
            }
        }

        public int SkontoFrist {
            get {
                int ret = 0;
                if (!ExistsName("Skontofrist")) {
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = MySheet.Cells[row, "AN"];
                        if (retBrutto.Value != null) {
                            string t1 = Convert.ToString(retBrutto.Value);
                            if (Extensions.isNumeric(t1, System.Globalization.NumberStyles.Any)) {
                                return Convert.ToInt32(t1);
                            } else {
                                return 0;
                            }
                        }
                        return 0;
                    }
                    return 0;
                } else {
                    Range t1 = MySheet.Range["Skontofrist"];
                    if (t1.Value != null) {
                        int.TryParse(t1.Value.ToString(), out ret);
                    }
                    return ret;
                }
            }
        }

        public double SkontoBetrag {
            get {
                if (!ExistsName("ZusatzkostenSkontobrutto")) {
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = MySheet.Cells[row, "AR"];
                        return retBrutto.Value;
                    }
                    return 0.0;
                } else {
                    Range t1 = MySheet.Range["ZusatzkostenSkontobrutto"];
                    return t1.Value;
                }
            }
        }

        public double SkontoProzentSatz {
            get {
                if (!ExistsName("SkontoProzentsatz")) {
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = MySheet.Cells[row, "AM"];
                        return retBrutto.Value;
                    }
                    return 0.0;
                } else {
                    Range t1 = MySheet.Range["SkontoProzentsatz"];
                    return t1.Value;
                }
            }
        }

        public string Straße {
            get {
                string ret = "";
                Range r1 = MySheet.Range[Settings.Default.ZelleStrasse];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
            set {
                Range r1 = MySheet.Range[Settings.Default.ZelleStrasse];
                r1.Value = value;
            }
        }

        public string Suchbegriff {
            get {
                string ret = Extensions.Left(Projekt, Projekt.IndexOf("p"));
                return ret;
            }
        }

        public double SummeBrutto {
            get {
                if (!ExistsName("SummeBrutto")) {
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[301, "AF"];
                    object toFind = "Summe brutto";
                    int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = MySheet.Cells[row, "AR"];
                        return retBrutto.Value;
                    }
                    return 0.0;
                } else {
                    Range t1 = MySheet.Range["SummeBrutto"];
                    return t1.Value;
                }
            }
        }

        public double SummeNetto {
            get {
                if (HasNames) {
                    if (!ExistsName("SummeNetto")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Summe netto";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            return retNetto.Value;
                        }
                        return 0.0;
                    }
                    Range t1 = MySheet.Range["SummeNetto"];
                    return t1.Value;

                }
                return 0.0;
            }
        }

        public double SummeLeistungen {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösSummeMontage")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Leistungen";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösSummeMontage"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double SummeProdukte {
            get {
                double ret = 0;
                if (HasNames) {
                    if (!ExistsName("ErlösSummeHandelsware")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Produkte";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = MySheet.Cells[row, "AR"];
                            Double.TryParse(retNetto.Value.ToString(), out ret);
                            return ret;
                        }
                        return ret;
                    }
                    Range t1 = MySheet.Range["ErlösSummeHandelsware"];
                    Double.TryParse(t1.Value.ToString(), out ret);
                    return ret;

                }
                return ret;
            }
        }

        public double SummeRabatte {
            get {
                double ret = 0;
                ret = this.RabattFix +
                      this.RabattLeistungenFix +
                      this.RabattLeistungenProzent +
                      this.RabattProdukteFix +
                      this.RabattProdukteProzent +
                      this.RabattProzent +
                      this.RabattZusatzProzent;
                return ret;
            }
        }

        public double SummeRabatteLeistungen {
            get {
                double ret = 0;
                ret = this.RabattLeistungenFix +
                      this.RabattLeistungenProzent;
                return ret;
            }
        }

        public double SummeRabatteProdukte {
            get {
                double ret = 0;
                ret = this.RabattProdukteFix +
                      this.RabattProdukteProzent;
                return ret;
            }
        }

        public string TempValue {
            get {
                string ret = "";
                Range r1 = MySheet.Range["A2"];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
        }

        public double Umsatzsteuer {
            get {
                if (HasNames) {
                    if (!ExistsName("Umsatzsteuer")) {
                        Range r1 = MySheet.Columns["AF"];
                        Range aCell = MySheet.Cells[301, "AF"];
                        object toFind = "Umsatzsteuer";
                        int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retUst = MySheet.Cells[row, "AR"];
                            return retUst.Value;
                        }
                        return 0.0;
                    }
                    Range t1 = MySheet.Range["Umsatzsteuer"];
                    return t1.Value;

                }
                return 0.0;
            }
        }

        public double UmsatzsteuerProzent {
            get {
                if (!ExistsName("Umsatzsteuer")) {
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[301, "AF"];
                    object toFind = "Umsatzsteuer";
                    int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retUst = MySheet.Cells[row, "AM"];
                        return retUst.Value;
                    }
                    return 0.0;
                }
                Range t1 = MySheet.Range["Umsatzsteuer"];
                Range t2 = MySheet.Cells[t1.Row, "AM"];
                return t2.Value;
            }
        }

        public List<AkontoObj> Zahlungen {
            get { return this.zahlungsList; }
        }

        public int ZahlungsFrist {
            get {
                int ret = 0;
                if (!ExistsName("Zahlungsfrist")) {
                    Range r1 = MySheet.Columns["AF"];
                    Range aCell = MySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(MySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = MySheet.Cells[row, "AO"];
                        if (retBrutto.Value != null) {
                            string t1 = Convert.ToString(retBrutto.Value);
                            if (Extensions.isNumeric(t1, System.Globalization.NumberStyles.Any)) {
                                return Convert.ToInt32(t1);
                            } else {
                                return 0;
                            }
                        }
                    }
                    return 0;
                } else {
                    Range t1 = MySheet.Range["Zahlungsfrist"];
                    if (t1.Value != null) {
                        int.TryParse(t1.Value.ToString(), out ret);
                    }
                    return ret;
                }
            }
        }

        public int ZaZeileSummeArbeitszeit {
            get;
            set;
        }

        public int ZaZeileSummeLeistungen {
            get;
            set;
        }

        public int ZaZeileSummeProjektLeistungen {
            get;
            set;
        }

        public int ZaZeileSummeInterneLeistungen {
            get;
            set;
        }

        public int ZeileAkonto {
            get;
            set;
        }

        public int ZeileDB {
            get;
            set;
        }

        public int ZeileSummeArbeitszeit {
            get;
            set;
        }

        public int ZeileSummeBrutto {
            get;
            set;
        }

        public int ZeileSummeGesamt {
            get;
            set;
        }

        public int ZeileSummeLeistungen {
            get;
            set;
        }

        public int ZeileSummeNetto {
            get;
            set;
        }

        #endregion

        #region Functions

        public bool ExistsName(string name) {
            foreach (Name n1 in MySheet.Names) {
                if (n1.Name.Contains(MySheet.Name) & n1.Name.Contains(name)) {
                    if (!n1.Value.Contains("#REF!")) {
                        return true;
                    }
                }
            }
            return false;
        }

        private int GetJeFreeRowNumber(Worksheet sh) {
            int startRow = 3;
            int freeRow = 0;
            try {
                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 5000) {
                    Range r1 = sh.Cells[startRow, 3];
                    if (r1.Value != null) {
                        if (!Extensions.isNumeric(r1.Value, System.Globalization.NumberStyles.Any)) {
                            if (((string)r1.Value).ToUpper().Contains("SUMME LEISTUNGEN")) {
                                freeRow = startRow;
                                startRowSelect = true;
                                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                                ;
                                Range row = sh.Rows[startRow - 1];
                                row.Select();
                                row.Copy();
                                row.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                            }
                        }
                    } else {
                        freeRow = startRow;
                        startRowSelect = true;
                    }
                    startRow++;
                }
            } catch (Exception ex) {
                Debug.Print(ex.Message);
            }
            return freeRow;
        }

        //public Range GetNewRow(Worksheet sh, int startRow) {
        //    Range row = sh.Rows[startRow];
        //    return row;
        //}

        private static int GetNextRowNumber(Worksheet sh, Range rCol, Range activeCell, object toFind) {
            return GetNextRowNumber(sh, rCol, activeCell, toFind, false);
        }

        private static int GetNextRowNumber(Worksheet sh, Range rCol, Range activeCell, object toFind, bool exact) {
            int ret = 0;
            Range r1;
            if (!exact) {
                r1 = rCol.Find(What: toFind, After: activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlPart);
            } else {
                r1 = rCol.Find(toFind, activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlWhole);
            }
            if (r1 != null) {
                ret = r1.Row;
            }
            return ret;
        }

        public Range GetRow(int rowNumber) {
            Range r1 = MySheet.Rows[rowNumber];
            return r1;
        }

        #endregion

        #region Methods

        public void AddAkonto(AkontoObj akonto) {
            this.akontoList.Add(akonto);
        }

        private void AddAllSheetRows(int startRow, bool isCalcSheet, bool intern, bool onlyWithJobDate) {

            bool startRowSelect = false;
            if (!intern) {
                if (isCalcSheet) {
                    while (startRowSelect == false & startRow < 10000) {
                        Range row = MySheet.Rows[startRow];
                        ProjectRow r1 = new ProjectRow(row);
                        if (r1.RowType.ToUpper() == "DB") {
                            startRowSelect = true;
                        } else {
                            if ((onlyWithJobDate & r1.JobDatum != new DateTime(1900, 1, 1)) | (!onlyWithJobDate)) {
                                r1.RowNumber = startRow;
                                MyRows.Add(r1);
                            }
                            startRow++;
                        }
                    }
                } else {
                    while (startRowSelect == false & startRow < 1000) {
                        Range row = MySheet.Rows[startRow];
                        ProjectRow r1 = new ProjectRow(row);
                        if (r1.KundenName == "") {
                            startRowSelect = true;
                        } else {
                            if ((onlyWithJobDate & r1.JobDatum != new DateTime(1900, 1, 1)) | (!onlyWithJobDate)) {
                                r1.RowNumber = startRow;
                                MyRows.Add(r1);
                            }
                            startRow++;
                        }
                    }
                }
            } else {
                while (startRowSelect == false & startRow < 10000) {
                    Range row = MySheet.Rows[startRow];
                    ProjectRow r1 = new ProjectRow(row);
                    Range c1 = row.Cells[1, "B"];
                    Range c2 = row.Cells[1, "C"];
                    string t1 = Extensions.Nz(Convert.ToString(c1.Value), "");
                    string t2 = t1 + Extensions.Nz(Convert.ToString(c2.Value), "");

                    if (t2.ToUpper().Contains("ARBEIT")) {
                        startRowSelect = true;
                    }
                    if (t2.ToUpper().Contains("LEISTUNGEN")) {
                        startRowSelect = true;
                    } else {
                        if ((onlyWithJobDate & r1.JobDatum != new DateTime(1900, 1, 1)) | (!onlyWithJobDate)) {
                            r1.RowNumber = startRow;
                            MyRows.Add(r1);
                        }
                        startRow++;
                    }

                }
            }
        }

        public void AddInvoice(InvoiceObj invoice) {
            this.invoiceList.Add(invoice);
        }

        public void AddMahnung(MahnungObj mahn) {
            this.mahnList.Add(mahn);
        }

        public void AddZahlung(AkontoObj zahlung) {
            this.zahlungsList.Add(zahlung);
        }

        public void ActivateSheet() {
            ((Microsoft.Office.Interop.Excel._Worksheet)MySheet).Activate();
        }

        public void CopyJob(string job, string subJob) {
            Range rCol = this.MySheet.Columns["AF"];
            Range aCell = this.MySheet.Cells[301, "AF"];
            Range r1 = null;
            Range r2 = null;

            object toFind = job;
            int jobRow = GetNextRowNumber(this.MySheet, rCol, aCell, toFind, true);
            if (jobRow == 0) {
                return;
            }
            int firstRow = jobRow;

            bool weiter = false;
            try {
                while (weiter == false) {
                    r1 = this.MySheet.Rows[jobRow];
                    r2 = r1.Cells[1, "AG"];
                    if (r2.Value != null) {
                        string t1 = Convert.ToString(r2.Value);
                        if (t1 != subJob) {
                            aCell = MySheet.Cells[jobRow, "AF"];
                            jobRow = GetNextRowNumber(MySheet, rCol, aCell, toFind, true);
                            if (jobRow == firstRow) {
                                weiter = true;
                            }
                        } else {
                            Range c1 = this.MySheet.Rows[jobRow];
                            c1.Select();
                            c1.Copy();
                            c1.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                            Range c2 = this.MySheet.Rows[jobRow + 1];
                            ProjectRow jobCopyRow = new ProjectRow(c2);
                            jobCopyRow.Durchführender1 = "";
                            jobCopyRow.Durchführender2 = "";
                            jobCopyRow.ResetJobDatum();
                            jobCopyRow.JobOrt = "";
                            jobCopyRow.Hyperlink = "";
                            jobCopyRow.RechnungsNummer = 0;
                            jobCopyRow.SetFontBold(true);
                            weiter = true;
                        }
                    } else {
                        weiter = true;
                    }
                }
            } catch (Exception ex) {
                Debug.Print(ex.Message);
                return;
            }
        }

        public void DeleteAllContent() {
            int startRow = 3;
            if (IsCalcSheet) {
                startRow = Settings.Default.ZeileStart;
            }
            Range r1 = MySheet.Rows[startRow.ToString() + ":" + "65000"];
            r1.ClearContents();
            r1.ClearComments();
        }

        public void DeleteAllRows() {
            foreach (ProjectRow row in MyRows) {
                MyRows.Remove(row);
            }
        }

        private void SetRowNumbers() {
            if (IsCalcSheet) {

                bool weiter = false;
                int startRow = Settings.Default.ZeileStart;
                while (!weiter & startRow < 5000) {
                    Range r1 = MySheet.Cells[startRow, "AF"];
                    string t1 = Extensions.Nz(Convert.ToString(r1.Value), "");
                    if (t1 != null) {
                        if (t1.ToUpper().Contains("GESAMT")) {
                            this.ZeileSummeGesamt = startRow;
                        }
                        if (t1.ToUpper().Contains("SUMME NETTO")) {
                            this.ZeileSummeNetto = startRow;
                        }
                        if (t1.ToUpper().Contains("SUMME BRUTTO")) {
                            this.ZeileSummeBrutto = startRow;
                        }
                        if (t1.ToUpper().Contains("AKONTO")) {
                            this.ZeileAkonto = startRow;
                        }
                        if (t1.ToUpper() == "DB") {
                            this.ZeileDB = startRow;
                            weiter = true;
                        }
                    }
                    startRow++;
                }
            } else {
                if (MySheet.Name.Contains("P") | MySheet.Name.Contains("M")) {
                    return;
                }
                if (((Workbook)MySheet.Parent).Name.Contains("le") | ((Workbook)MySheet.Parent).Name.Contains("pl") |
                    ((Workbook)MySheet.Parent).Name.Contains("il") | ((Workbook)MySheet.Parent).Name.Contains("je") | MySheet.Name.Contains("Leistungen")) {
                    bool weiter = false;
                    int startRow = Settings.Default.ZeileStartLeistungsDateien;
                    while (!weiter & startRow < 5000) {
                        Range r1 = MySheet.Cells[startRow, "B"];
                        Range r2 = MySheet.Cells[startRow, "C"];

                        string t1 = "";
                        string t2 = "";
                        if (r1.Value != null) {
                            t1 = Convert.ToString(r1.Value);
                        }
                        if (r2.Value != null) {
                            t2 = t1 + Convert.ToString(r2.Value);
                        }
                        if (t1 != "") {
                            if (t1.ToUpper().Contains("ARBEITSZEIT")) {
                                this.ZeileSummeArbeitszeit = startRow;
                            }
                            if (t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                                this.ZeileSummeLeistungen = startRow;
                                weiter = true;
                            }
                        }
                        if (t2 != "") {
                            if (t2.ToUpper().Contains("ARBEITSZEIT")) {
                                this.ZeileSummeArbeitszeit = startRow;
                            }
                            if (t2.ToUpper().Contains("SUMME LEISTUNGEN")) {
                                this.ZeileSummeLeistungen = startRow;
                                weiter = true;
                            }
                        }
                        startRow++;
                    }

                }
            }
        }

        public void HiddenConditionRows() {
            if (IsCalcSheet) {
                try {
                    for (int i = this.ZeileSummeGesamt + 1; i < this.ZeileSummeNetto; i++) {
                        Range r1 = MySheet.Rows[i];
                        ProjectRow row = new ProjectRow(r1);
                        if (row.SummeVp == 0) {
                            r1.EntireRow.Hidden = true;
                        }
                    }
                } catch (Exception ex) {
                    Debug.Print(ex.Message);
                }
            }
        }

        public void HiddenSonderConditionRows() {
            if (IsCalcSheet) {
                try {
                    for (int i = this.ZeileSummeBrutto + 1; i < this.ZeileAkonto; i++) {
                        Range r1 = MySheet.Rows[i];
                        ProjectRow row = new ProjectRow(r1);
                        if (row.SummeVp == 0) {
                            r1.EntireRow.Hidden = true;
                        }
                    }
                } catch (Exception ex) {
                    Debug.Print(ex.Message);
                }
            }
        }

        private void LoadAkontos() {
            if (!IsCalcSheet) {
                return;
            }
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Akonto";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range akAmount = MySheet.Cells[rowLs1, "AR"];
                if (akAmount.Value != null && akAmount.Value != 0.0) {
                    Range akDate = MySheet.Cells[rowLs1, "AH"];
                    string t1 = Convert.ToString(akDate.Value);
                    if (akDate.Value != null) {
                        DateTime dDate;
                        if (DateTime.TryParse(t1, out dDate)) {
                            AkontoObj ak = new AkontoObj(Math.Abs(akAmount.Value), dDate);
                            this.AddAkonto(ak);
                        }
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadAbnahmeprotokollDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Abnahmeprotokoll";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            toFind = "Leistungen";
            int maxRow = GetNextRowNumber(MySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range apDate = MySheet.Cells[rowLs1, "K"];
                if (apDate.Value != null) {
                    if (rowLs1 < maxRow) {
                        abnahmeProtokollDates.Add(apDate.Value);
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                toFind = "Abnahmeprotokoll";
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadAbnahmeprotokollDatesRep() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Abnahmeprotokoll  Reparatur";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            toFind = "Administration";
            int maxRow = GetNextRowNumber(MySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {

                if (ApDate1 != null) {
                    if (rowLs1 < maxRow) {
                        if (!abnahmeProtokollDates.Contains(DurchfuehrungVon)) {
                            abnahmeProtokollDates.Add(DurchfuehrungVon);
                        }
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                toFind = "Abnahmeprotokoll  Reparatur";
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadAuftragsDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Auftragsbestätigung";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range apDate = MySheet.Cells[rowLs1, "K"];
                if (apDate.Value != null) {
                    Range r2 = MySheet.Cells[rowLs1, "AG"];
                    if (r2.Value != null) {
                        string t1 = Convert.ToString(r2.Value);
                        if (t1.Contains("Verkauf")) {
                            auftragsBestaetigungsDatum = apDate.Value;
                        }
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadCheckReparatur() {
            if (!IsCalcSheet) {
                return;
            }


            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Systemerfassung";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range r2 = MySheet.Cells[rowLs1, "AG"];
                if (r2.Value != null) {
                    string t1 = Convert.ToString(r2.Value);
                    if (t1.Contains("Reparatur")) {
                        reparatur = true;
                        return;
                    } else {
                        break;
                    }
                } else {
                    break;
                }
            }
            if (rowLs1 == 0) {
                return;
            }
            aCell = MySheet.Cells[rowLs1, "AF"];
            rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            if (rowLs1 == firstRow) {
                return;
            }
        }

        private void LoadDurchführungsDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Logistik";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind, true);
            if (rowLs1 == 0) {
                rowLs1 = 301;
            }

            aCell = MySheet.Cells[rowLs1, "AF"];
            toFind = "Leistungen";
            int rowLs2 = GetNextRowNumber(MySheet, rCol, aCell, toFind, true);
            if (rowLs2 > rowLs1) {
                Range rDate;
                for (int i = rowLs1; i < rowLs2; i++) {
                    rDate = MySheet.Cells[i, "K"];
                    if (rDate.Value != null) {
                        if (!durchfuehrungsDates.Contains(rDate.Value)) {
                            durchfuehrungsDates.Add(rDate.Value);
                        }
                    }
                }
            }

        }

        private void LoadInvoices() {
            if (!IsCalcSheet) {
                return;
            }
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            object toFind = "Abrechnung";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            int firstRow = rowLs1;
            int lastRow = rowLs1;
            while (rowLs1 != 0) {
                Range sub = MySheet.Cells[rowLs1, "AG"];

                if (sub.Value != null && sub.Value.ToString() == "Kunde") {
                    Range invDate = MySheet.Cells[rowLs1, "K"];
                    if (invDate.Value != null && invDate.Value != new DateTime(1900, 1, 1)) {
                        Range invNo = MySheet.Cells[rowLs1, "S"];
                        if (invNo.Value != null && invNo.Value != 0) {
                            int invNr = 0;
                            DateTime dDate;
                            if (DateTime.TryParse(invDate.Value.ToString(), out dDate)) {
                                Range user = MySheet.Cells[rowLs1, "N"];
                                int.TryParse(invNo.Value.ToString(), out invNr);
                                InvoiceObj inv = new InvoiceObj(invNr, 0.0, dDate);
                                inv.TpUser = user.Value;
                                this.AddInvoice(inv);
                            }
                        }
                    }
                    aCell = MySheet.Cells[rowLs1, "AF"];
                    lastRow = rowLs1;
                    rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                    if (rowLs1 == lastRow || rowLs1 == firstRow) {
                        return;
                    }
                } else {
                    lastRow = rowLs1;
                    aCell = MySheet.Cells[rowLs1, "AF"];
                    rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                    if (rowLs1 == lastRow || rowLs1 == firstRow) {
                        return;
                    }
                }
            }
        }

        private void LoadLieferscheinDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            object toFind = "Lieferschein";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            toFind = "Leistungen";
            int maxRow = GetNextRowNumber(MySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range lsDate = MySheet.Cells[rowLs1, "K"];
                if (lsDate.Value != null) {
                    if (rowLs1 < maxRow) {
                        lieferscheinDates.Add(lsDate.Value);
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                toFind = "Lieferschein";
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadLieferscheinDatesRep() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            object toFind = "Lieferschein Reparatur";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            toFind = "Administration";
            int maxRow = GetNextRowNumber(MySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                if (LsDate1 != null) {
                    if (rowLs1 < maxRow) {
                        if (!lieferscheinDates.Contains(DurchfuehrungVon)) {
                            lieferscheinDates.Add(DurchfuehrungVon);
                        }
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                toFind = "Lieferschein Reparatur";
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadMahnungen() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Mahnung";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            int lastRow = rowLs1;
            while (rowLs1 != 0) {
                Range apDate = MySheet.Cells[rowLs1, "K"];
                if (apDate.Value != null) {
                    DateTime dDate;
                    if (DateTime.TryParse(apDate.Value.ToString(), out dDate)) {
                        Range anw = MySheet.Cells[rowLs1, "AG"];
                        string t1 = Convert.ToString(anw.Value);
                        Range user = MySheet.Cells[rowLs1, "N"];
                        if (t1.Contains("Anwalt")) {
                            MahnungObj mahn = new MahnungObj(dDate, true);
                            mahn.TpUser = user.Value;
                            this.AddMahnung(mahn);
                        } else {
                            MahnungObj mahn = new MahnungObj(dDate);
                            mahn.TpUser = user.Value;
                            this.AddMahnung(mahn);
                        }
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == lastRow) {
                    return;
                }
            }
        }

        private void LoadZahlungen() {
            if (!IsCalcSheet) {
                return;
            }
            Range rCol = MySheet.Columns["AF"];
            Range aCell = MySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Zahlung";
            int rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range akAmount = MySheet.Cells[rowLs1, "AR"];
                if (akAmount.Value != null && akAmount.Value != 0.0) {
                    Range akDate = MySheet.Cells[rowLs1, "AH"];
                    string t1 = Convert.ToString(akDate.Value);
                    if (akDate.Value != null && !t1.Contains("Rest")) {
                        DateTime dDate;
                        if (DateTime.TryParse(t1, out dDate)) {
                            AkontoObj ak = new AkontoObj(Math.Abs(akAmount.Value), dDate);
                            this.AddZahlung(ak);
                        }
                    }
                }
                aCell = MySheet.Cells[rowLs1, "AF"];
                rowLs1 = GetNextRowNumber(MySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        //public void Write(string bereich, Worksheet sourceSheet, GetFreeNumber getFreeNumber, bool withFormats) {
        //    if (MyRows.Count != 0) {

        //        foreach (ProjectRow row in MyRows) {
        //            int freeRowNumber = getFreeNumber(bereich, MySheet.);
        //            Range sourceRow = sourceSheet.Rows[row.RowNumber];
        //            Range targetRow = GetNewRow(MySheet, freeRowNumber);

        //            ((Microsoft.Office.Interop.Excel._Worksheet)sourceSheet).Activate();
        //            sourceRow.Select();
        //            sourceRow.Copy();
        //            ((Microsoft.Office.Interop.Excel._Worksheet)MySheet.).Activate();
        //            targetRow.Select();
        //            targetRow.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
        //            targetRow.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
        //            if (withFormats) {
        //                targetRow.PasteSpecial(XlPasteType.xlPasteFormats, Operation: XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
        //            }

        //        }
        //    }
        //}

        //public void WriteJob(string job, string subJob, DateTime jobDate, string lf, string userName,
        //        int rechnungsNummer, string fileName, string display) {
        //    WriteJob(job, subJob, jobDate, lf, userName, rechnungsNummer, fileName, display, "", false);
        //}

        //public void WriteJob(string job, string subJob, DateTime jobDate, string lf, string userName,
        //        int rechnungsNummer, string fileName, string display, string user2, bool exported) {

        //    Range rCol = this.MySheet.Columns["AF"];
        //    Range aCell = this.MySheet.Cells[301, "AF"];
        //    Range r1 = null;
        //    //Range r2 = null;
        //    object toFind = job;
        //    int jobRow = GetNextRowNumber(this.MySheet, rCol, aCell, toFind, true);
        //    if (jobRow == 0) {
        //        return;
        //    }
        //    int firstRow = jobRow;
        //    r1 = this.MySheet.Rows[jobRow];

        //    bool weiter = false;
        //    try {
        //        while (weiter == false) {

        //            ProjectRow myRow = new ProjectRow(r1);

        //            if (myRow.Job == job && myRow.JobDetail == subJob) {

        //                if (myRow.LeistungExported) {
        //                    Range newR1 = MySheet.Rows[jobRow, Missing.Value];
        //                    ((Microsoft.Office.Interop.Excel._Worksheet)MySheet.).Activate();
        //                    newR1.Select();
        //                    newR1.Copy();
        //                    newR1.Insert(Shift: XlInsertShiftDirection.xlShiftDown);

        //                    newR1 = MySheet.Rows[jobRow + 1, Missing.Value];
        //                    myRow = new ProjectRow(newR1);

        //                }
        //                myRow.JobDatum = jobDate;
        //                //myRow.Leistung = job;
        //                myRow.Durchführender1 = userName;
        //                myRow.Durchführender2 = user2;
        //                myRow.JobOrt = lf;

        //                if (rechnungsNummer != 0) {
        //                    myRow.RechnungsNummer = rechnungsNummer;
        //                }

        //                if (!String.IsNullOrEmpty(fileName)) {
        //                    Range r3 = r1.Cells[1, "J"];
        //                    r3.Hyperlinks.Add(r3, Address: fileName, TextToDisplay: display);
        //                }
        //                myRow.SetFontBold(false);
        //                if (exported) {
        //                    if (myRow.JobEvidenz) {
        //                        myRow.SetExported("Je");
        //                    } else {
        //                        myRow.SetExported("Le");
        //                    }
        //                } else {
        //                    myRow.SetExported("");
        //                }
        //                weiter = true;
        //            } else {
        //                aCell = this.MySheet.Cells[jobRow, "AF"];
        //                jobRow = GetNextRowNumber(this.MySheet, rCol, aCell, toFind, true);
        //                r1 = this.MySheet.Rows[jobRow];
        //                if (jobRow == firstRow) {
        //                    weiter = true;
        //                }
        //            }

        //        }



        //    } catch (Exception ex) {
        //        //log.Add("WriteJob(" + jobRow.ToString() + ")", ex);
        //        Debug.Print(ex.Message);
        //    }
        //}

        #endregion

    }
}
